package lib.persistence;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import lib.Field;

public class ExcelConnector {
	private String xlsPath;
	private String worksheet;
	private Workbook workbook;
	public ExcelConnector(String xlsPath) throws Exception
	{
		this.xlsPath = xlsPath;
		workbook = Workbook.getWorkbook(new File(xlsPath));
	}
	public void setWorksheet(String worksheet)
	{
		this.worksheet = worksheet;
	}
	public List<String> getColumnNames()
	{
		return getContents(0);
	}
	private ArrayList<String> getContents(int rowNumber)
	{
		Sheet sheet = workbook.getSheet(worksheet);
		ArrayList<String> output = new ArrayList<String>();
		int i = 0;
		while(cellIsNotEmpty(sheet,i,rowNumber))
		{
			Cell c = sheet.getCell(i,rowNumber);
			output.add(c.getContents());
			i++;
		}
		return output;
	}
	private boolean cellIsNotEmpty(Sheet sheet, int cellNumber, int rowNumber) {
		Cell c = null;
		try {
			c = sheet.getCell(cellNumber, rowNumber);	
		} catch (Exception e) {
			return false;
		}
		if (c.getContents().trim().length() > 0)
			return true;
		return false;
	}
	public List<Field> getRow(int rowNumber)
	{
		List<String> columns = getColumnNames();
		ArrayList<String> contents = getContents(rowNumber);
		ArrayList<Field> row = new ArrayList<Field>();
		int i = 0;
		for (String column : columns) {
			row.add(new Field(column ,contents.get(i)));
			i++;
		}
		return row;
	}
	public String getWorksheet() 
	{
		return worksheet;
	}
	public List<List<Field>> getAllRows() 
	{
		List<List<Field>> rows = new ArrayList<List<Field>>();
		int rowNumber = 1;
		while(rowExists(rowNumber))
		{
			rows.add(getRow(rowNumber));
			rowNumber++;
		}
		return rows;
	}
	private boolean rowExists(int rowNumber) 
	{
		ArrayList<String> contents = getContents(rowNumber);
		if (contents.size() == 0)
			return false;
		if (contents.get(0).equalsIgnoreCase(""))
			return false;
		return true;
	}
}
